{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Functions\n",
    "Each database engine has a different set of built-in functions that allows us to manipulate data. \n",
    "In this section, we are going to describe some popular functions per data type:\n",
    "\n",
    "## Numeric functions in Mysql\n",
    "### ABS\t\n",
    "Returns the absolute value of a number.\n",
    "\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT ABS(-4.5)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "4.5\n",
    "```\n",
    "\n",
    "### CEIL\n",
    "Returns the smallest integer value that is >= to a number\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT CEIL(4.5)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "5\n",
    "```\n",
    "\n",
    "\n",
    "### DIV\t\n",
    "Used for integer division.\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT FLOOR(7/2)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "3\n",
    "```\n",
    "### FLOOR\t\n",
    "Returns the largest integer value that is <= to a number\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT FLOOR(4.5)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "4\n",
    "```\n",
    "### GREATEST\t\n",
    "Returns the greatest value of the list of arguments\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT GREATEST(4.5, 5.5)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "5.5\n",
    "```\n",
    "\n",
    "### LEAST\t\n",
    "Returns the smallest value of the list of arguments\n",
    "For Instance:\n",
    "\n",
    "```sql\n",
    "SELECT LEAST(4.5, 5.5)\n",
    "```\n",
    "\n",
    "Returns:\n",
    "```\n",
    "4.5\n",
    "```\n",
    "\n",
    "## String functions in Mysql\n",
    "\n",
    "### LENGTH\n",
    "Returns the length of a string (in characters):\n",
    "```sql\n",
    "SELECT LENGTH(Name)\n",
    "FROM Person\n",
    "```\n",
    "will return the lengths of the names of persons. For instance, if we had two records with names \"Mark Grayson\" and \"Eve Wilkins\":\n",
    "\n",
    "```\n",
    "12\n",
    "11\n",
    "```\n",
    "\n",
    "### CONCAT\t\n",
    "Adds two or more srings together:\n",
    "```sql\n",
    "SELECT CONCAT(Person.Name, Order.OrderId)\n",
    "FROM Person INNER JOIN ORDER ON Person.PersonId = Order.Person_fk\n",
    "```\n",
    "will return a string concatenating the name of the person and the order ID. For instance:\n",
    "\n",
    "```\n",
    "\"Mark Grayson1123\"\n",
    "```\n",
    "\n",
    "### CONCAT_WS\n",
    "Adds two or more strings together with a separator. If you tried the function above, you would probably get the feeling that the result is not pretty. It is better to concat with a separator:\n",
    "```sql\n",
    "SELECT CONCAT(' - ', Person.Name, Order.OrderId)\n",
    "FROM Person INNER JOIN ORDER ON Person.PersonId = Order.Person_fk\n",
    "```\n",
    "This will create a string of the type:\n",
    "```\n",
    "\"Mark Grayson - 1123\"\n",
    "```\n",
    "\n",
    "### LCASE\n",
    "Converts a string to lower-case. For instance:\n",
    "```sql\n",
    "SELECT LCASE(Name)\n",
    "FROM Person\n",
    "```\n",
    "\n",
    "with the data above would return:\n",
    "```\n",
    "\"mark grayson\"\n",
    "\"eve wilkins\"\n",
    "```\n",
    "### LEFT\n",
    "Extracts a number of characters from a string (starting from left):\n",
    "\n",
    "```sql\n",
    "SELECT LEFT(Name, 3)\n",
    "FROM Person\n",
    "```\n",
    "\n",
    "with the data above would return:\n",
    "```\n",
    "\"mar\"\n",
    "\"eve\"\n",
    "```\n",
    "\n",
    "### LOCATE\t\n",
    "Returns the position of the first occurrence of a substring in a string. For instance, say we would like to know which is the position of the first space:\n",
    "\n",
    "```sql\n",
    "SELECT LOCATE(\" \", Name)\n",
    "FROM Person\n",
    "```\n",
    "\n",
    "```\n",
    "5\n",
    "4\n",
    "```\n",
    "\n",
    "### LPAD\t\n",
    "Left-pads a string with another string, to a certain length:\n",
    "\n",
    "For instance:\n",
    "```sql\n",
    "SELECT LPAD(\"EDEM\", 20, \"#\");\n",
    "```\n",
    "Returns:\n",
    "```\n",
    "\"################EDEM\"\n",
    "```\n",
    "\n",
    "### POSITION\t\n",
    "Returns the position of the first occurrence of a substring in a string\n",
    "```sql\n",
    "SELECT POSITION(\"M\" IN \"EDEM\");\n",
    "```\n",
    "Returns:\n",
    "```\n",
    "4\n",
    "```\n",
    "### REPLACE\t\n",
    "Replaces all occurrences of a substring within a string, with a new substring\n",
    "```sql\n",
    "SELECT REPLACE(\"EDEM\", \"E\", \"A\");\n",
    "```\n",
    "Returns:\n",
    "```\n",
    "\"ADAM\"\n",
    "```\n",
    "\n",
    "### REVERSE\t\n",
    "Reverses a string and returns the result:\n",
    "```sql\n",
    "SELECT REVERSE(\"EDEM\");\n",
    "```\n",
    "Returns:\n",
    "```\n",
    "\"MEDE\"\n",
    "```\n",
    "\n",
    "### RIGHT\t\n",
    "Extracts a number of characters from a string (starting from right). \n",
    "\n",
    "```sql\n",
    "SELECT RIGHT(\"MySQL is Awesome\", 3);\n",
    "\n",
    "```\n",
    "Returns: \n",
    "```\n",
    "\"ome\"\n",
    "```\n",
    "\n",
    "### RPAD\t\n",
    "Right-pads a string with another string, to a certain length. For instance:\n",
    "\n",
    "```sql\n",
    "SELECT RPAD(\"EDEM\", 10, \"#\");\n",
    "```\n",
    "\n",
    "Returns: \n",
    "\n",
    "```\n",
    "\"EDEM######\"\n",
    "```\n",
    "\n",
    "### RTRIM\t\n",
    "Removes trailing spaces from a string: \n",
    "```sql\n",
    "SELECT RTRIM(\"EDEM     \");\n",
    "```\n",
    "\n",
    "Returns:\n",
    " \n",
    "\"EDEM\"\n",
    "\n",
    "### STRCMP\t\n",
    "Compares two strings: \n",
    "\n",
    "```sql\n",
    "SELECT STRCMP(\"EDEM\", \"EDEM\");\n",
    "```\n",
    "\n",
    "Returns: \n",
    "0\n",
    "\n",
    "### SUBSTR\t\n",
    "Extracts a substring from a string (starting at any position): \n",
    "\n",
    "```sql\n",
    "SELECT SUBSTR(\"EDEM\", 1, 2);\n",
    "```\n",
    "\n",
    "Returns: \n",
    "```\n",
    "\"ED\"\n",
    "```\n",
    "\n",
    "### TRIM\t\n",
    "Removes leading and trailing spaces from a string: \n",
    "\n",
    "```sql\n",
    "SELECT TRIM(\"  EDEM    \");\n",
    "```\n",
    "\n",
    "Returns: \n",
    "```\n",
    "\"EDEM\"\n",
    "```\n",
    "\n",
    "### UCASE\t\n",
    "Converts a string to upper-case:\n",
    "\n",
    "```sql\n",
    "SELECT UCASE(\"edem\", 1, 2);\n",
    "```\n",
    "\n",
    "Returns: \n",
    "```\n",
    "\"EDEM\"\n",
    "```\n",
    "\n",
    "## Date type functions\n",
    "### ADDTIME\t\n",
    "Adds a time interval to a time/datetime and then returns the time/datetime\n",
    "\n",
    "### DATEDIFF\t\n",
    "Returns the number of days between two date values\n",
    "\n",
    "### DATE_ADD\t\n",
    "Adds a time/date interval to a date and then returns the date\n",
    "\n",
    "### DATE_FORMAT\t\n",
    "Formats a date\n",
    "\n",
    "### DATE_SUB\t\n",
    "Subtracts a time/date interval from a date and then returns the date\n",
    "\n",
    "### DAY\t\n",
    "Returns the day of the month for a given date\n",
    "\n",
    "### DAYNAME\t\n",
    "Returns the weekday name for a given date\n",
    "\n",
    "### HOUR\t\n",
    "Returns the hour part for a given date\n",
    "\n",
    "### LOCALTIME\t\n",
    "Returns the current date and time\n",
    "\n",
    "### NOW\t\n",
    "Returns the current date and time\n",
    "\n",
    "### TIME_FORMAT\tFormats a time by a specified format\n",
    "\n",
    "### TIMEDIFF\t\n",
    "Returns the difference between two time/datetime expressions\n",
    "\n",
    "### TIMESTAMP\t\n",
    "Returns a datetime value based on a date or datetime value\n",
    "\n",
    "### WEEKOFYEAR\t\n",
    "Returns the week number for a given date\n",
    "\n",
    "### YEARWEEK\t\n",
    "Returns the year and week number for a given date\n",
    "\n",
    "\n",
    "## Conditional statement functions\n",
    "This section describes another set of functions allow us to build programming logic into our statements.\n",
    "\n",
    "### IF\n",
    "The IF function returns one value if the condition is met, otherwise another value:\n",
    "\n",
    "```sql\n",
    "# IF(condition, value_if_true, value_if_false)\n",
    "SELECT IF(500<1000, \"True\", \"False\");\n",
    "```\n",
    "\n",
    "### IFNULL\n",
    "The IFNULL function returns the value of a field or expression if it is not null, otherwise it returns a default value provided as parameter:\n",
    "\n",
    "```sql\n",
    "# IFNULL(value, value_if_null)\n",
    "SELECT IFNULL(Order.Person_fk, \"N/A\");\n",
    "```\n",
    "### CASE\n",
    "Case statements allow us to build more sophisticated conditional statements.\n",
    "\n",
    "For instance, imagine we want to add a new column \"Category\" to our query, labeling orders into three categories \"Large\", \"Medium\", and \"Small\", depending on the quantity of the order:\n",
    "\n",
    "```sql\n",
    "SELECT OrderID, Quantity,\n",
    "CASE\n",
    "    WHEN Quantity > 30 THEN \"Large\" \n",
    "\n",
    "    WHEN Quantity = 30 THEN \"Medium\"\n",
    "    ELSE \"small\"\n",
    "END AS Category\n",
    "FROM OrderDetails;\n",
    "```\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "${KERNEL_SPEC_DISPLAY_NAME}",
   "language": "${KERNEL_SPEC_LANGUAGE}",
   "name": "${KERNEL_SPEC_NAME}"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "source": [],
    "metadata": {
     "collapsed": false
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}